create database bank;
use bank;

create table branch
   (branch_name 	varchar(15)	not null unique,
    borough 	varchar(15)	not null,
    assets 			decimal(9,2)		not null,
    primary key(branch_name));

insert into branch	values ('Fort Green',	'Brooklyn',	 900000);
insert into branch	values ('Stapleton',	'Staten Island',	2100000);
insert into branch	values ('Todt Hill',	'Staten Island',	1700000);
insert into branch	values ('Harlem',	'Manhattan',	 400200);
insert into branch	values ('Park Slope',	'Brooklyn',	8000000);

create table customer
   (customer_ID		char(3),
    customer_name 	varchar(15)	not null,
    customer_address 	varchar(12)	not null,
    customer_ZIP 	char(5)		not null,
    customer_borough 	varchar(15)	not null,
    primary key(customer_id));

insert into customer	values ('111','Ji','1 Oxford St','11238','Brooklyn');
insert into customer	values ('222','Borodin','28 V Blvd','10314', 'Staten Island');
insert into customer	values ('333','Chandra','6 Metro St','11201','Brooklyn');
insert into customer	values ('444','Smith','4 Neth Ave','10463','Bronx');
insert into customer	values ('555','Ade','475 Wash Ave','11231','Brooklyn');
insert into customer    values ('666','Saul','475 Wash Ave','11231','Brooklyn');


create table account
   (account_number 	char(5),
    branch_name		varchar(15)	not null,
    balance 		decimal(9,2) 	not null,
    primary key(account_number));

insert into account	values ('11111','Fort Green','500.55');
insert into account	values ('22222','Todt Hill','700.77');
insert into account	values ('33333','Harlem','400.19');
insert into account	values ('44444','Fort Green','350.46');
insert into account	values ('55555','Harlem','900.23');
insert into account	values ('66666','Stapleton','1900.89');

create table loan
   (loan_number 	char(2),
    branch_name		varchar(15)	not null,
    amount 		decimal(9,2)	not null,
    primary key(loan_number));
    
insert into loan values ('11','Fort Greene',1000.99);
insert into loan values ('22','Harlem',2000.23);
insert into loan values ('33','Harlem',1500.54);
insert into loan values ('44','Fort Greene',1500.15);
insert into loan values ('55','Park Slope',500.00);
insert into loan values ('66','Stapleton',900.99);

create table depositor
   (customer_ID 	char(3)	not null,
    account_num 	char(5)	not null,
  primary key(customer_ID, account_num),
  constraint foreign key(account_num) references account(account_number),
  constraint foreign key(customer_ID) references customer(customer_ID));

insert into depositor values ('111','11111');
insert into depositor values ('111','66666');
insert into depositor values ('222','11111');
insert into depositor values ('222','22222');
insert into depositor values ('222','66666');
insert into depositor values ('222','44444');
insert into depositor values ('333','55555');
insert into depositor values ('333','33333');
insert into depositor values ('666','33333');


create table borrower
   (customer_ID 	varchar(15)	not null,
    loan_number 	varchar(15)	not null,
    primary key(customer_ID, loan_number),
  constraint foreign key(customer_ID) references customer(customer_ID),
  constraint foreign key(loan_number) references loan(loan_number));

insert into borrower values ('666',	'11');
insert into borrower values ('111',	'11');
insert into borrower values ('333',	'22');
insert into borrower values ('444',	'44');
insert into borrower values ('222',	'55');
insert into borrower values ('333',	'55');
insert into borrower values ('555',	'66');

